package com.cwvs.dao;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.commons.collections.MapUtils;
import org.apache.commons.lang.StringUtils;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import com.cwvs.utils.MySqlPageUtil;
import com.cwvs.utils.Pages;

/**
 * 
 * 用户相关DAO
 */
@Repository
public class AppFeedbackDao {
	@Resource
	private JdbcTemplate jdbcTemplate;
	@Resource
	private MySqlPageUtil mySqlPageUtil;
	/**
	 * 列表
	 * 
	 * @return
	 */
	public Pages list(Map<String, Object> m, Pages page) {
		StringBuilder sql = new StringBuilder();
		sql.append("  SELECT T1.ID,T1.QUESTION,T1.CTIME ");
		sql.append(" ,T2.IP_ADDRESS,T3.USERNAME ");
		sql.append("  FROM APP_FEEDBACK T1 ");
		sql.append(" LEFT JOIN APP_TOKEN T2 ON T1.TOKEN=T2.ID ");
		sql.append(" LEFT JOIN APP_USER T3 ON T3.ID=T2.USER_ID ");
		sql.append("  WHERE 1=1  ");
		List<Object> params = new ArrayList<Object>();
		if (StringUtils.isNotBlank(MapUtils.getString(m, "USERNAME"))) {
			sql.append(" AND UPPER(T3.USERNAME) LIKE ? ");
			params.add("%"+MapUtils.getString(m, "USERNAME").toUpperCase()+"%");
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "QUESTION"))) {
			sql.append(" AND UPPER(T1.QUESTION) LIKE ? ");
			params.add("%"+MapUtils.getString(m, "QUESTION").toUpperCase()+"%");
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "CTIME"))) {
			sql.append(" AND DATE_FORMAT(T1.CTIME,'%Y-%m-%d') >= ? ");
			params.add(MapUtils.getString(m, "CTIME"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "ETIME"))) {
			sql.append(" AND DATE_FORMAT(T1.CTIME,'%Y-%m-%d') <= ? ");
			params.add(MapUtils.getString(m, "ETIME"));
		}
		String page_sql = mySqlPageUtil.getPageSql(page, sql.toString());
		List<Map<String, Object>> ls = jdbcTemplate.queryForList(page_sql,
				params.toArray());
		page.setPage(ls);
		page.setTotalRecord(userCount(m));
		return page;
	}

	private int userCount(Map<String, Object> m) {
		StringBuffer sql = new StringBuffer();
		sql.append(" SELECT COUNT(1) FROM APP_FEEDBACK T1  ");
		sql.append(" LEFT JOIN APP_TOKEN T2 ON T1.TOKEN=T2.ID ");
		sql.append(" LEFT JOIN APP_USER T3 ON T3.ID=T2.USER_ID ");
		sql.append("  WHERE 1=1  ");
		List<Object> params = new ArrayList<Object>();
		if (StringUtils.isNotBlank(MapUtils.getString(m, "USERNAME"))) {
			sql.append(" AND UPPER(USERNAME) LIKE ? ");
			params.add("%"+MapUtils.getString(m, "USERNAME").toUpperCase()+"%");
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "QUESTION"))) {
			sql.append(" AND UPPER(T1.QUESTION) LIKE ? ");
			params.add("%"+MapUtils.getString(m, "QUESTION").toUpperCase()+"%");
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "CTIME"))) {
			sql.append(" AND DATE_FORMAT(T1.CTIME,'%Y-%m-%d') >= ? ");
			params.add(MapUtils.getString(m, "CTIME"));
		}
		if (StringUtils.isNotBlank(MapUtils.getString(m, "ETIME"))) {
			sql.append(" AND DATE_FORMAT(T1.CTIME,'%Y-%m-%d') <= ? ");
			params.add(MapUtils.getString(m, "ETIME"));
		}
		return jdbcTemplate.queryForInt(sql.toString(), params.toArray());
	}
}
